from code.data.Homework import Homework
from code.dao.user_management import __check_user_exist
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback
import datetime


def add_homework(homework: Homework):
    if homework.userID is None:
        logging.error("HomeworkID is required.")
        return False
    if not __check_user_exist(homework.userID):
        return False
    if homework.content is None:
        content = "NULL"
    else:
        content = "'" + homework.content + "'"
    if homework.file_path is None:
        file_path = "NULL"
    else:
        file_path = "'" + homework.file_path.replace("\\", "\\\\") + "'"
    if homework.file_size is None:
        file_size = "NULL"
    else:
        file_size = homework.file_size
    sql = "INSERT INTO homework(content, file_path, file_size, userID) VALUES ({}, {}, {}, '{}')".format(
        content, file_path, file_size, homework.userID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting homework into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Homework is added with SQL: " + sql)
    return True


def search_homework(homework: Homework, search_by: str = "all", fuzzy_search=False):
    homeworks = []
    if search_by == "all":
        sql = "SELECT * FROM homework"
    elif search_by == "homeworkID":
        if homework.homeworkID is None:
            logging.error("HomeworkID is required.")
            return None
        sql = "SELECT * FROM homework WHERE homeworkID = {}".format(homework.homeworkID)
    elif search_by == "time":
        if homework.time is None:
            logging.error("Time is required.")
            return None
        sql = "SELECT * FROM homework WHERE time = '{}'".format(homework.time)
        if fuzzy_search:
            sql = "SELECT * FROM homework WHERE time LIKE '%{}%'".format(homework.time)
    elif search_by == "userID":
        if homework.userID is None:
            logging.error("UserID is required.")
            return None
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "homeworkID", "time", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Homework is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for homework failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        homeworks.append(Homework(result[0], result[1], result[2], result[3], result[4], result[5]))
    logging.info("Search homework with SQL: {}".format(sql))
    return homeworks


def update_homework(homework: Homework, update_by: str):
    if homework.homeworkID is None:
        logging.error("HomeworkID is required.")
        return False
    if not __check_homework_exit(homework.homeworkID):
        return False
    if update_by == "content":
        if homework.content is None:
            logging.error("Content is required.")
            return False
        sql = "UPDATE homework SET content = '{}' WHERE homeworkID = {}".format(homework.content, homework.homeworkID)
    elif update_by == "file_path":
        if homework.file_path is None:
            logging.error("File_path is required.")
            return False
        file_path = homework.file_path.replace("\\", "\\\\")
        sql = "UPDATE homework SET file_path = '{}' WHERE homeworkID = {}".format(file_path, homework.homeworkID)
    elif update_by == "file_size":
        if homework.file_size is None:
            logging.error("File_size is required.")
            return False
        sql = "UPDATE homework SET file_size = {} WHERE homeworkID = {}".format(homework.file_size, homework.homeworkID)
    else:
        logging.error("Attribute for update_by is not exist, use one in "
                      '["content", "file_path", "file_size"]')
        return False
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Update homework from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Homework is updated with SQL: {}".format(sql))
    return True


def delete_homework(homework: Homework):
    if homework.homeworkID is None:
        logging.error("HomeworkID is required.")
        return False
    if not __check_homework_exit(homework.homeworkID):
        return False
    sql = "DELETE FROM homework WHERE homeworkID = {}".format(homework.homeworkID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete homework from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Homework is deleted with SQL: {}".format(sql))
    return True


def __check_homework_exit(homeworkID: int):
    sql = "SELECT * FROM homework WHERE homeworkID = {}".format(homeworkID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("Homework {} is not exist".format(homeworkID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for homework failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == "__main__":
    homework1 = Homework(userID="admin")
    homework2 = Homework(content="This is a test", file_path="D:\\test.file", file_size=1024, userID="admin")
    print(add_homework(homework1))
    print(add_homework(homework2))
    result1 = search_homework(Homework())
    for result in result1:
        print(result.to_string())
    result2 = search_homework(Homework(homeworkID=2), search_by="homeworkID")
    for result in result2:
        print(result.to_string())
    homework3 = Homework(homeworkID=2, content="Change the content and\n the second line.")
    print(update_homework(homework3, "fake"))
    print(update_homework(homework3, "content"))
    print(delete_homework(Homework(homeworkID=1)))
